package com.dao;

import com.model.Author;
import com.model.Book;
import com.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class AuthorDao {
    public List<Author> listAuthors(Connection con) throws SQLException {
        String sql = "select a.id, a.name, a.email, b.name as b_name\n" +
                "from author a\n" +
                "         left join r_author_book rab on a.id = rab.author_id\n" +
                "         left join book b on rab.book_id = b.id\n" +
                "order by a.id";
        PreparedStatement pre = null;
        ResultSet rs = null;
        List<Author> authors = new ArrayList<>();
        try {
            rs = DBUtil.query(con, pre, rs, sql, null);
            while (rs.next()) {
                Author author = new Author();
                author.setId(rs.getInt("id"));
                author.setName(rs.getString("name"));
                author.setEmail(rs.getString("email"));
                author.setBookName(rs.getString("b_name"));
                authors.add(author);
            }
        } catch (SQLException e) {
            throw e;
        }
        DBUtil.closeResource(null, pre, rs);
        return authors;
    }
    public List<Author> searchAuthor(Connection con,String name) throws SQLException {
        String sql = "select a.id, a.name, a.email, b.name as b_name\n" +
                "from author a\n" +
                "         left join r_author_book rab on a.id = rab.author_id\n" +
                "         left join book b on rab.book_id = b.id\n" +
                "    where a.name =?";
        PreparedStatement pre = null;
        ResultSet rs = null;
        List<Author> authors = new ArrayList<>();
        try {
            Object[] obj={name};
            rs = DBUtil.query(con, pre, rs, sql, obj);
            while (rs.next()) {
                Author author = new Author();
                author.setId(rs.getInt("id"));
                author.setName(rs.getString("name"));
                author.setEmail(rs.getString("email"));
                author.setBookName(rs.getString("b_name"));
                authors.add(author);
            }
        } catch (SQLException e) {
            throw e;
        }
        DBUtil.closeResource(null, pre, rs);
        return authors;
    }

    public int addAuthor(Connection con, Author author) throws SQLException {
        Object[] obj = {author.getName(), author.getEmail()};
        String sql = "insert into author(`name`, `email`) values(?, ?)";
        PreparedStatement pre = null;
        int updateRows = 0;

        try {
            updateRows = DBUtil.execute(con, pre, sql, obj);
        } catch (SQLException e) {
            throw e;
        }

        DBUtil.closeResource(null, pre, null);
        return updateRows;
    }

    public int updateAuthor(Connection con, Author author) throws SQLException {
        Object[] obj = {author.getName(), author.getEmail(), author.getId()};
        String sql = "update author set name=?,email=? where id=?";
        PreparedStatement pre = null;
        int updateRows = 0;

        try {
            updateRows = DBUtil.execute(con, pre, sql, obj);
        } catch (SQLException e) {
            throw e;
        }

        DBUtil.closeResource(null, pre, null);
        return updateRows;
    }

    public int deleteAuthorById(Connection con, int id) throws SQLException {
        Object[] obj = {id};
        String sql = "delete from author where id=?";
        PreparedStatement pre = null;
        int updateRows = 0;

        try {
            updateRows = DBUtil.execute(con, pre, sql, obj);
        } catch (SQLException e) {
            throw e;
        }

        DBUtil.closeResource(null, pre, null);
        return updateRows;
    }

}
